"""
-- 1 Задание. даны таблицы emp и dep. необходимо посчитать количество однофамильцев в отделах.
-- итоговый результат:
Бухгалтерия, 5
Кадры, 2
"""

from db_utils import execute_query


def execute_task_1():
    db_name = 'local_db.sqlite'
    query = '''
        SELECT dep_name, SUM(dupl_count)
        FROM(
            SELECT dep.name AS dep_name, COUNT(emp.name) AS dupl_count
            FROM emp
            JOIN dep ON emp.dep_id = dep.id
            GROUP BY dep.id, emp.name
            HAVING COUNT(*) > 1
        ) AS subquery
        GROUP BY dep_name
    '''

    results = execute_query(db_name, query)

    for row in results:
        print(row)


if __name__ == '__main__':
    execute_task_1()
